package my.db.DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import config.EstablishConnection;
import my.db.DTO.ArticleDTO;
import my.db.DTO.AuthenticationDTO;
import my.db.DTO.AuthorDTO;

public class AuthorDAO {
	private PreparedStatement pstmt=null;
	private ResultSet rs=null;
	private Statement stmt;
	private AuthenticationDTO auDTO=null;
	private AuthorDTO adto=null;
	private EstablishConnection econ=null;
	/*
	 * Constructor Initialized
	 */
	public AuthorDAO(){
		econ=EstablishConnection.CreateInstance();
	}
	/*
	 * Getting only user mail
	 * 	if true
	 * 		return object AuthorDTO
	 * 	else
	 * 		return null
	 */
	public AuthorDTO getUserMail(String mail){
		String query = "Select UserMail From Users Where UserMail=?";
		AuthorDTO dto=null;
		try{
			pstmt = econ.conection.prepareStatement(query);
			pstmt.setString(1,mail);
			rs=pstmt.executeQuery();
			if(rs.next()){
				dto=new AuthorDTO();
				dto.setAuthorMail(rs.getString("UserMail"));
			}
			return dto;
		}catch(SQLException e){
			return null;
		}
	}
	/*
	 * Insert new author
	 * 	if true
	 * 		return true
	 * 	else
	 * 		return false
	 */
	public boolean addNewAuthor(String name,int gender,String mail,String phone,int status,java.util.Date date){
		String sql="Insert Into Users Values(userid.nextval,?,?,?,?,?,?)";	
		try{
			pstmt=econ.conection.prepareStatement(sql);		
			pstmt.setString(1,name);
			pstmt.setInt(2,gender);
			pstmt.setString(3, mail);
			pstmt.setString(4,phone);
			pstmt.setInt(5, status);
			pstmt.setDate(6, new java.sql.Date(date.getTime()));
			pstmt.executeUpdate();
			pstmt.close();
			return true;
			}catch(SQLException e){	
				return false;
			}
	}
	/*
	 * Getting all user info
	 * 	if true
	 * 		return object Arraylist of AuthorDTO
	 * 	else
	 * 		return null
	 */
	public int countRowsAuthor(String keyword) throws SQLException{
		int num = 0;
		String sql="";
		if (keyword == null || keyword == ""){
			sql = "select count(u.userid) from users u,account a where u.userid=a.userid and Status=1 and a.position='author'";
		}else{
			//sql = "select count(userid) from users where status=1 and upper(userid) like '%" + keyword + "%' or upper(username) like '%"+keyword +"%' or upper(userdate) like '%"+keyword +"%'";
			sql="select count(u.userid) from users u,account a where u.userid=a.userid and Status=1 and a.position='author' and and upper(userid) like '%" + keyword + "%' or upper(username) like '%"+keyword +"%' or upper(userdate) like '%"+keyword +"%'";
		}
		pstmt = econ.conection.prepareStatement(sql);
		rs = pstmt.executeQuery(sql);
		if (rs.next()) {
			num = rs.getInt(1);
		}
		
		return num;
	}
	public ArrayList<AuthorDTO> getListAuthor(int start,int stop,String keyword){
		ArrayList<AuthorDTO> lst_ad=new ArrayList<AuthorDTO>();
		
		String sql="";
		if (keyword == null || keyword == ""){
			System.out.println("No search");
			sql = "select * from(select a.*, rownum rnum from (select u.* from users u,account a where u.userid=a.userid and Status=1 and a.position='author' order by u.Userid DESC)a where rownum <="+stop+")where rnum>="+start+"";
		}else{
			keyword=keyword.toUpperCase();
			String subSql = "select u.* from users u,account a where u.userid=a.userid and Status=1 and a.position='author' and UPPER(userid) like '%"+keyword+"%' or  UPPER(userName) like '%"+keyword+"%' or UPPER(userDate) like '%"+keyword+"%' order by u.Userid DESC";
			sql="select * from(select a.*, rownum rnum from ("+ subSql +")a where rownum <="+stop+")where rnum>="+start+"";
		}
		try{
			pstmt=econ.conection.prepareStatement(sql);	
			rs=pstmt.executeQuery();
			while(rs.next()){
				adto=new AuthorDTO();
				adto.setAuthorID(rs.getInt(1));
				adto.setAuthorName(rs.getString(2));
				adto.setAuthorDate(rs.getDate(7));
				adto.setAuthorStatus(rs.getInt(6));
				lst_ad.add(adto);
				adto=null;
			}			
		}catch(SQLException e){
			return null;
		}finally{
			if(pstmt!=null)try{pstmt.close();}catch(Exception e){}
			if(rs!=null)try{rs.close();}catch(Exception e){}
		}
		return lst_ad;
	}
	/*
	 * Getting author info and account info
	 * 	if true
	 * 		return object AuthenticationDTO
	 * 	else
	 * 		return null
	 */
	public AuthenticationDTO listAutorIfo(int id){
		String sql="Select A.AccountID,U.UserID,U.UserName,U.UserGender,U.UserMail,U.UserPhone,A.Accountusername,A.AccountPassword,A.Position From ACCOUNT A,USERS U "
				+ "Where A.userid=U.userid "
				+ "and U.UserID=? ";
		AuthenticationDTO rDto=null;
		try{
			pstmt=econ.conection.prepareStatement(sql);		
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
			if(rs.next()){
				AuthorDTO a=new AuthorDTO();
				rDto=new AuthenticationDTO();
				rDto.setAccountID(rs.getInt("AccountID"));
				rDto.setAccountName(rs.getString("ACCOUNTUSERNAME"));
				rDto.setPosition(rs.getString("POSITION"));
				rDto.setAccountPassword(rs.getString("AccountPassword"));
				a.setAuthorID(rs.getInt("UserID"));
				a.setAuthorName(rs.getString("UserName"));
				a.setAuthorMail(rs.getString("UserMail"));
				a.setAuthorPhone(rs.getString("UserPhone"));
				a.setAuthorGender(rs.getInt("UserGender"));
				rDto.setAutDTO(a);
			}
			return rDto;
			}catch(SQLException e){	
				return null;				
	}
}
	/*
	 * Getting author info by specific 
	 * 	if true
	 * 		return object AuthorDTO
	 * 	else
	 * 		return null
	 */
	public AuthorDTO getAuthor(int id){
		String sql="select * from users where userid=?";
		AuthorDTO a=null;
		try{
			pstmt=econ.conection.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
			if(rs.next()){
				a=new AuthorDTO();
				a.setAuthorID(rs.getInt(1));
				a.setAuthorName(rs.getString(2));
				a.setAuthorGender(rs.getInt(3));
				a.setAuthorMail(rs.getString(4));
				a.setAuthorPhone(rs.getString(5));
				a.setAuthorDate(rs.getDate(7));
			}			
			if(a==null)System.out.println("Author null");
			else System.out.println("author exist");
		}catch(SQLException e){
			return null;
		}finally{
			if(pstmt!=null)try{pstmt.close();}catch(Exception e){}
			if(rs!=null)try{rs.close();}catch(Exception e){}
		}
		return a;
	}
	/*
	 * Edit and update author info
	 * 	if true
	 * 		return true
	 * 	else
	 * 		return false
	 */
	public boolean updateAuthor(AuthorDTO author) {
		String sql="Update Users Set UserName=?,UserGender=?,UserMail=?,UserPhone=? Where UserID=?";
		try {
			pstmt=econ.conection.prepareStatement(sql);
			pstmt.setString(1, author.getAuthorName());
			pstmt.setInt(2,author.getAuthorGender());
			pstmt.setString(3,author.getAuthorMail());
			pstmt.setString(4, author.getAuthorPhone());
			pstmt.setInt(5, author.getAuthorID());
			pstmt.executeUpdate();
			pstmt.close();
			return true;
		} catch (SQLException e) {
			return false;
		}
	}
	
	/*
	 * Removeing author by specific info
	 * 	if true
	 * 		return object AuthenticationDTO
	 * 	else
	 * 		return null
	 */
	public boolean delete(String id){
		String sql="Update Users Set Status=1-Status WHERE USERID In("+id+")";	
		try{
			pstmt=econ.conection.prepareStatement(sql);		
			pstmt.executeUpdate();
			pstmt.close();
			return true;
			}catch(SQLException e){	
				return false;
			}
	}
}
